package cn.nwpu.eckel.Dao;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.nwpu.eckel.model.MPS;
import cn.nwpu.eckel.model.Plan;

import static cn.nwpu.eckel.utility.DBConnection.close;
import static cn.nwpu.eckel.utility.DBConnection.getConnection;

public class PlanDao {
	public static List<Plan> getPlansByMpsNo(int mpsNo){
		List<Plan> plans=new ArrayList<Plan>();
		Plan tempPlan;
		Connection con=getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		String sql="select * from plan_tb where mpsNo=?";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, mpsNo);
			rs=pstmt.executeQuery();
			while(rs.next()){
				tempPlan=new Plan(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4),
						          rs.getInt(5),rs.getInt(6),rs.getInt(7),rs.getInt(8),
						          rs.getInt(9),rs.getInt(10),rs.getInt(11),rs.getInt(12));
				plans.add(tempPlan);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
		return plans;
	}
	
	public static void insertPlans(List<Plan> plans){
		Connection con=getConnection();
		PreparedStatement pstmt=null;
		String sql="insert into plan_tb values(?,?,?,?,?,?,?,?,?,?,?,?)";
		for(Plan p:plans){
			try{
				pstmt=con.prepareStatement(sql);
				pstmt.setInt(1,p.ts);
				pstmt.setInt(2,p.fc);
				pstmt.setInt(3,p.odr);
				pstmt.setInt(4,p.gr);
				pstmt.setInt(5,p.sr);
				pstmt.setInt(6,p.pabI);
				pstmt.setInt(7,p.pab);
				pstmt.setInt(8,p.nr);
				pstmt.setInt(9,p.mps);
				pstmt.setInt(10,p.por);
				pstmt.setInt(11,p.atp);
				pstmt.setInt(12,p.mpsNo);
				pstmt.executeUpdate();
			}catch(SQLException e){
				e.printStackTrace();
			}finally{
				close(pstmt);
			}
		}
		close(con);
	}
	
	
	public static void deletePlans(int mpsNo){
		Connection con=getConnection();
		PreparedStatement pstmt=null;
		String sql="delete from plan_tb where mpsNo=?";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1,mpsNo);
			pstmt.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt);
			close(con);
		}
	}
	
	public static void alterPlans(int mpsNo,List<Plan> plans){
		PlanDao.deletePlans(mpsNo);
		PlanDao.insertPlans(plans);
	}
	public static Plan roll(int mpsNo){
		MPS mps=MPSDao.getMPSById(mpsNo);
		mps.loadPlans();
		Plan p=new Plan();
		Connection con=getConnection();
		PreparedStatement pstmt_1=null;
		PreparedStatement pstmt_2=null;
		PreparedStatement pstmt_3=null;
		PreparedStatement pstmt_4=null;
		ResultSet rs=null;
		String sql_1="select * from plan_tb where mpsNo=?&ts=0";
		String sql_2="delete from plan_tb where mpsNo=? and ts=0";
		String sql_3="update plan_tb set ts=ts-1 where mpsNo=?";
		String sql_4="insert into plan_tb values(?,0,0,0,0,?,?,0,0,0,0,?)";
		try{
			pstmt_1=con.prepareStatement(sql_1);
			pstmt_2=con.prepareStatement(sql_2);
			pstmt_3=con.prepareStatement(sql_3);
			pstmt_4=con.prepareStatement(sql_4);
			
			pstmt_1.setInt(1,mpsNo);
			rs=pstmt_1.executeQuery();
			
			pstmt_2.setInt(1,mpsNo);
			pstmt_2.executeUpdate();
			
			pstmt_3.setInt(1,mpsNo);
			pstmt_3.executeUpdate();
			
			pstmt_4.setInt(1,mps.ph);
			pstmt_4.setInt(2,mps.p[mps.ph].pabI);
			pstmt_4.setInt(3,mps.p[mps.ph].pab);
			pstmt_4.setInt(4,mpsNo);
			pstmt_4.executeUpdate();
			
			while(rs.next()){
				p.ts=rs.getInt(1);
				p.fc=rs.getInt(2);
				p.odr=rs.getInt(3);
				p.gr=rs.getInt(4);
				p.sr=rs.getInt(5);
				p.pabI=rs.getInt(6);
				p.pab=rs.getInt(7);
				p.nr=rs.getInt(8);
				p.mps=rs.getInt(9);
				p.por=rs.getInt(10);
				p.atp=rs.getInt(11);
				p.mpsNo=rs.getInt(12);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			close(pstmt_1);
			close(pstmt_2);
			close(con);
		}
		return p;
	}
}
